/********CREATE CUSTOMERS PROCEDURE*****/
CREATE PROC spInsertCustomer
@Name AS NVARCHAR(30),
@Type AS TINYINT,
@Address AS NVARCHAR(30),
@Phone AS VARCHAR(20),
@Email AS VARCHAR(50),
@Relationship AS VARCHAR(100)
AS
	INSERT INTO Customer(cName,cType,cAddress,cPhone,cEmail,cRelationship) 
	VALUES(@Name,@Type,@Address,@Phone,@Email,@Relationship)

GO

CREATE PROC spUpdateCustomer
@Code AS VARCHAR(10),
@Name AS NVARCHAR(30),
@Type AS TINYINT,
@Address AS NVARCHAR(30),
@Phone AS VARCHAR(20),
@Email AS VARCHAR(50),
@Relationship AS VARCHAR(100)
AS
	UPDATE Customer SET cName = @Name,cType=@Type,cAddress=@Address,cPhone=@Phone,cEmail=@Email,cRelationship=@Relationship
	WHERE cID = @Code

GO

CREATE PROC spGetAllCustomers
AS
	SELECT * FROM Customer
GO

CREATE PROC spSearchCustomers
   @customerType INT,
   @name VARCHAR(20)
AS
   SELECT *
     FROM Customer
    WHERE (cType = @customerType OR @customerType = -1)
      AND (cName LIKE '%' + @name + '%' OR len(cName) = 0)



GO

CREATE PROC spDeleteCustomer
@Code AS INT
AS
	DELETE FROM Customer WHERE cID = @Code

GO

CREATE PROC spGetNameCustomer
AS
	SELECT cID,cName FROM Customer

/********CREATE SUPPLIERS PROCEDURE*****/
CREATE PROC spInsertSupplier
@Name AS NVARCHAR(30),
@FullName AS NVARCHAR(100),
@Phone AS VARCHAR(12),
@Fax AS VARCHAR(12),
@Type AS TINYINT,
@Address AS NVARCHAR(50),
@Email AS VARCHAR(50),
@Website AS VARCHAR(50)
AS
	INSERT INTO Supplier(sName,sFullName,sPhone,sFax,sType,sAddress,sEmail,sWebsite) 
	VALUES(@Name,@FullName,@Phone,@Fax,@Type,@Address,@Email,@Website)

GO

CREATE PROC spUpdateSupplier
@Code AS VARCHAR(10),
@Name AS NVARCHAR(30),
@FullName AS NVARCHAR(100),
@Phone AS VARCHAR(12),
@Fax AS VARCHAR(12),
@Type AS TINYINT,
@Address AS NVARCHAR(50),
@Email AS VARCHAR(50),
@Website AS VARCHAR(50)
AS
	UPDATE Supplier SET sName = @Name,sFullName = @FullName,sPhone = @Phone,sFax = @Fax, sType = @Type, sAddress=@Address,sEmail=@Email,sWebsite = @Website
	WHERE sID = @Code

GO


CREATE PROC spSearchSuppliers
   @supplierType INT,
   @name VARCHAR(20)
AS
   SELECT *
     FROM Supplier
    WHERE (sType = @supplierType OR @supplierType = -1)
      AND (sName LIKE '%' + @name + '%' OR len(sName) = 0)

GO

CREATE PROC spGetAllSuppliers
AS
	SELECT * FROM Supplier

GO

CREATE PROC spDeleteSupplier
@Code AS INT
AS
	DELETE FROM Supplier WHERE sID = @Code
GO
/********CREATE PRODUCTS PROCEDURE*****/
CREATE PROC [dbo].[spInsertProduct]
@Name AS VARCHAR(50),
@Type AS VARCHAR(50),
@ProductionDate AS DATETIME,
@ExpiryDate AS DATETIME,
@ImportDate AS DATETIME,
@Price AS FLOAT,
@Tax AS	FLOAT,
@Quantity AS INT,
@SellPrice AS FLOAT,
@Description AS NVARCHAR(1000)
AS
	INSERT INTO Product(pName,pType,pProductionDate,pExpiryDate,pImportDate,pPrice,pTax,pQuantity,pSellPrice,pDescription) 
	VALUES(@Name,@Type,@ProductionDate,@ExpiryDate,@ImportDate,@Price,@Tax,@Quantity,@SellPrice,@Description)

GO

CREATE PROC spUpdateProduct
@Code AS INT,
@Name AS VARCHAR(50),
@Type AS VARCHAR(50),
@ProductionDate AS DATETIME,
@ExpiryDate AS DATETIME,
@ImportDate AS DATETIME,
@Price AS FLOAT,
@Tax AS	FLOAT,
@Quantity AS INT,
@SellPrice AS FLOAT,
@Description AS NVARCHAR(1000)
AS
	UPDATE Product SET  pName=@Name,
						pType=@Type,
						pProductionDate =@ProductionDate,
						pExpiryDate= @ExpiryDate,
						pImportDate= @ImportDate,
						pPrice= @Price,
						pTax= @Tax,
						pQuantity= @Quantity,
						pSellPrice= @SellPrice,
						pDescription = @Description
	WHERE pID = @Code

GO

CREATE PROC spDeleteProduct
@Code AS INT
AS
	DELETE FROM Product WHERE pID = @Code

GO

CREATE PROC spGetAllProducts
AS
	SELECT * FROM Product
GO


CREATE PROC spSearchProduct
@Name VARCHAR(50)
AS
	SELECT pID, pName, pType, pProductionDate, pExpiryDate, pImportDate, pPrice, pTax, pQuantity, pSellPrice, pDescription 
			FROM Product 
			WHERE (pName LIKE '%' + @Name + '%')


GO


/********CREATE ORDER PROCEDURE*****/

create proc spInsertOrder
(
	@cID int,
	@bDeliveryDate datetime,
	@bDerliveryAddress varchar(50),
	@bDiscount float,
	@bPaid float ,
	@bUnpaid float,
	@bDate DateTime ,
	@bOrder bit ,
	@newID int Output
)
as
insert into Bill values (@cId, @bDeliveryDate, @bDerliveryAddress,@bDiscount, @bPaid, @bUnpaid, @bDate, @bOrder )
select @newId = @@IDENTITY

GO

create proc spUpdateOrder  
(
	 @bID int,  
	 @bDeliveryDate datetime,  
	 @bDeliveryAddress varchar(50),  
	 @bPaid float ,  
	 @bUnpaid float,  
	 @bDate DateTime,   
     @bStatus int	
)
as
Update Bill set bDeliveryDate = @bDeliveryDate, bDeliveryAddress = @bDeliveryAddress, bPaid = @bPaid , bUnpaid= @bUnpaid, bDate = @bDate, bStatus = @bStatus where bId = @bID  

GO

create proc spSelectOrder    
as
SELECT dbo.Bill.bID, dbo.Customer.cName, dbo.Bill.bDeliveryDate, dbo.Bill.bDeliveryAddress, bDiscount, bPaid, bUnpaid, dbo.Bill.bDate, dbo.Bill.bStatus   
FROM  dbo.Bill INNER JOIN    
   dbo.Customer ON dbo.Bill.cID = dbo.Customer.cID 


GO

Create proc spSelectOrderDetails
(
	@bID int
)

AS
SELECT     dbo.BillDetails.bId, dbo.BillDetails.pID, dbo.BillDetails.bdPrice, dbo.BillDetails.bdQuantity, dbo.Product.pName
FROM         dbo.Product INNER JOIN
                      dbo.BillDetails ON dbo.Product.pID = dbo.BillDetails.pID
Where  bId= @bID

GO

CREATE proc spInsertBillDetails
(  
 @bID int,  
 @pID int,  
 @bdPrice float,  
 @bdQuantity int,
 @bdDate datetime 
)  
AS  
Insert Into BillDetails Values(@bID, @pId, @bdPrice, @bdQuantity,@bdDate)  

GO

create proc spDeleteOrder
@id int
as
delete from Bill where @id= bID

GO

create proc spSearchOrder
(
	@cName varchar(100),
	@price1 float,
	@price2 float,
	@date1 datetime,
	@date2 datetime
)
as
select b.bID, c.cName, b.bDeliveryDate, b.bDeliveryAddress, b.bDiscount, b.bPaid, b.bUnpaid, b.bDate, b.bStatus  
From Bill b 
inner join Customer c on b.cId = c.cID
where (len(@cName) = 0 OR (c.cName like '%'+@cName +'%'))
	AND (@price1 = -1 OR (b.bpaid + b.bUnpaid) >= @price1)
    AND (@price2 = -1 OR (b.bpaid + b.bUnpaid) <= @price2)
	AND (@date1 is null OR b.bDeliveryDate >= @date1)
    AND (@date2 is null OR b.bDeliveryDate <= @date2)

GO

/********CREATE PURCHASE PROCEDURE*****/

create proc spDeletePurchase
@id int
as
delete from purchase where @id= pcID

GO

create proc spSelectPurchase
as
SELECT dbo.Purchase.pcID, dbo.Supplier.sFullName, dbo.Purchase.pcDate, dbo.Purchase.pPaid, dbo.Purchase.pUnpaid
FROM  dbo.Purchase INNER JOIN
	  dbo.Supplier ON dbo.Purchase.sID = dbo.Supplier.sID

GO

Create proc [dbo].[spSelectPurchaseDetails]
(
	@pcID int
)

AS
SELECT     dbo.PurchaseDetails.pcId, dbo.PurchaseDetails.pID, dbo.PurchaseDetails.pcdPrice, dbo.PurchaseDetails.pcdQuatity, dbo.Product.pName
FROM         dbo.Product INNER JOIN
                      dbo.PurchaseDetails ON dbo.Product.pID = dbo.PurchaseDetails.pID
Where  pcId= @pcid


GO

create proc [dbo].[spInsertPurchaseDetails]  
(  
 @pcID int,  
 @pID int,  
 @pPrice float,  
 @pQuantity int,
 @pcDate datetime 
)  
AS  
Insert Into PurchaseDetails Values(@pcID, @pId, @pPrice, @pQuantity,@pcDate)  

GO

create proc [dbo].[spInsertPurchase]
(
	@sID int,
	@paid float,
	@unpaid float,
	@pcdate DateTime,
    @newId int OUTPUT
)
AS
Insert into Purchase Values (@sID,@paid, @unpaid, @pcdate)
select @newId = @@IDENTITY

GO

create proc spUpdatePurchase
(
	@pcId int,
	@pPaid float,
	@pUnpaid float,
	@pcDate datetime
)
as
Update Purchase set pPaid = @pPaid , pUnpaid= @pUnpaid, pcDate = @pcDate where pcId = @pcID

GO

create proc spSearchPurchase
(
	@sName varchar(100),
	@price1 float,
	@price2 float,
	@date1 datetime,
	@date2 datetime
)
as
select p.pcID, s.sName, p.pcDate, p.pPaid, p.pUnpaid
From Purchase p 
inner join Supplier s on p.sId = s.sID
where (len(@sName) = 0 OR (s.sName like '%'+@sName +'%') OR (s.sFullName like '%'+@sName +'%'))
	AND (@price1 = -1 OR (ppaid + pUnpaid) >= @price1)
    AND (@price2 = -1 OR (ppaid + pUnpaid) <= @price2)
	AND (@date1 is null OR pcDate >= @date1)
    AND (@date2 is null OR pcDate <= @date2)

GO

create proc spUpdateAddQuantity
(
	@pID int,
	@pQuantity int
)
AS
Update Product set pQuantity = (pQuantity + @pQuantity) where pID = @pID

GO

create proc spUpdateBuyQuantity
(
	@pID int,
	@pQuantity int
)
AS
Update Product set pQuantity = (pQuantity - @pQuantity) where pID = @pID

GO


create proc spSelectExpiryDate
as
select * from Product order by pExpiryDate asc

GO
--------------------------
create proc spSelectDeliveryDate
as
select dbo.Bill.bID, dbo.Customer.cName, dbo.Bill.bDeliveryDate, bPaid, bUnpaid, dbo.Bill.bStatus   
FROM  dbo.Bill INNER JOIN    
   dbo.Customer ON dbo.Bill.cID = dbo.Customer.cID 
order by bDeliveryDate asc
